package com.cindata

import com.cindata.dimension.YearLimitDimension
import grails.converters.JSON
import grails.gorm.transactions.Transactional
import org.springframework.security.access.annotation.Secured

import java.text.SimpleDateFormat

@Transactional
@Secured(['isAuthenticated()'])
class AssessAnalyzeController
{

    def springSecurityService
    def componentService
    def dataSource
    def apiService
    static allowedMethods = [save: "POST", update: "PUT", delete: "DELETE"]

    //压力分析
    def stressAnalyze(Integer max){
        def city = params['city']
        def cityNameList = componentService.evaluate(Component.findByCode('showCity'))
        def cityList = City.findAllByNameInList(cityNameList,[sort: 'name', order: 'asc'])

        if(!city && cityList){
            params.cityName=cityList[0].name
            params.city=cityList[0].zoneNumber
        }else if(city && city!='-1'){
            params.cityName = City.findByZoneNumber(city)?.name
        }

        respond model: [params: params], view: 'stressAnalyze'
    }

    //压力测试数据
    def stressAnalyzeData(){
        def status = "success"
        def result = new HashMap()
        def resultList = new ArrayList()
        try {
            def city = params["city"]
            def priceFluctuationsRatio = params["priceFluctuationsRatio"]
            //获得上个月的日期
            def date = new Date()
            def simpleDateFormat = new SimpleDateFormat('YYYY-MM')
            Calendar calendar = Calendar.getInstance()
            calendar.setTime(date)
            calendar.set(Calendar.MONTH, calendar.get(Calendar.MONTH) - 1)
            date = calendar.getTime()
            def indexDate = simpleDateFormat.format(date).replace('-','')
            println "indexDate="+indexDate
            //价格排行
            def standardPriceRankingData = apiService.findStandardPriceRanking(city,"50","30")?.data
            //价指数排行
            def indexRankingData = apiService.findIndexRanking(city,"Month","2","price_index","esf",indexDate,"30")?.data
            def allPriceMomRatio = 0
            indexRankingData?.each{
                allPriceMomRatio += it?.priceMomRatio
            }
            standardPriceRankingData?.each{
                def map = new HashMap()
                map.districtCode = it?.districtCode
                map.districtName = it?.districtName
                map.standardPrice = it?.standardPrice
                map.priceMomRatio = 0
               resultList.add(map)
            }
            resultList?.each{
                Map map ->
                    indexRankingData?.each{
                        if(it?.districtCode == map?.districtCode){
                            map.priceMomRatio = (it?.priceMomRatio/allPriceMomRatio)*Double.parseDouble(priceFluctuationsRatio)
                        }
                    }
            }
            result.resultList = resultList
        }catch (Exception e){
            e.printStackTrace()
            status = "false"
            result.resultList = resultList
        }

        render([result: result, status: status] as JSON)
    }

    //压力测试新数据
    def stressAnalyzeNewData(){
        def status = "success"
        def result = new HashMap()
        def resultList = new ArrayList()
        try {
            def city = params["city"]
            def priceFluctuationsRatio = params["priceFluctuationsRatio"]
            //价格排行
            def standardPriceRankingData = apiService.findStandardPriceRanking(city,"50","30")?.data
            //城市价指数排行
            def cityIndexRankingData = apiService.findIndexCycleRanking(city,"Month","1","price_index","esf","","12")?.data["strType"]
            println "cityIndexRankingData:==="+cityIndexRankingData
            //城市下行政区
            def districtList = District.findAllByParentAreaAndAreaLevel(City.findByZoneNumberAndAreaLevel(city, 2), 3)
            if(standardPriceRankingData){
                standardPriceRankingData?.each{
                    def map = new HashMap()
                    map.districtCode = it?.districtCode
                    map.districtName = it?.districtName
                    map.standardPrice = it?.standardPrice
                    map.priceMomRatio = 0
                    resultList.add(map)
                }
            }else if(districtList){
                districtList?.each{
                    def map = new HashMap()
                    map.districtCode = it?.zoneNumber
                    map.districtName = it?.name
                    map.standardPrice = 0
                    map.priceMomRatio = 0
                    resultList.add(map)
                }
            }
            //系数分母
            def fenMu = 0
            cityIndexRankingData?.each{
                fenMu += it?.priceIndex*it?.priceIndex
            }
            if(fenMu==0){
                println "系数分母为0"
                result.resultList = resultList
                render([result: result, status: status] as JSON)
            }
            //println "fenMu++==="+fenMu
            districtList?.each{
                //系数分子
                def fenZi = 0
                //行政区价指数排行
                def districtIndexRankingData = apiService.findIndexCycleRanking(city,"Month","2","price_index","esf",it?.zoneNumber,"12")?.data["strType"]
                districtIndexRankingData?.each{
                    Map districtIndex ->
                    cityIndexRankingData?.each{
                        Map map ->
                            if(districtIndex?.indexDate == map?.indexDate){
                                fenZi +=  districtIndex?.priceIndex*map?.priceIndex
                            }
                    }

                }
                //println "fenZi++==="+fenZi
                resultList?.each{
                    Map resultData ->
                        if(Integer.parseInt(it?.zoneNumber) == Integer.parseInt(resultData?.districtCode)){
                            if(districtIndexRankingData && priceFluctuationsRatio){
                                resultData.priceMomRatio = fenZi/fenMu*(Double.parseDouble(priceFluctuationsRatio)+1)*cityIndexRankingData[12-5-1].priceIndex/districtIndexRankingData[12-5-1].priceIndex-1
                            }else if(districtIndexRankingData && !priceFluctuationsRatio){
                                resultData.priceMomRatio = districtIndexRankingData[0].priceIndex/districtIndexRankingData[12-6-1].priceIndex-1
                            }

                        }
                }
            }
            println "resultList==============="+resultList
            result.resultList = resultList
        }catch (Exception e){
            e.printStackTrace()
            status = "false"
            result.resultList = resultList
        }

        render([result: result, status: status] as JSON)
    }

    //资产跌破指数数据
    def assetsBelowData(){
        def status = "success"
        def resultList = new HashMap()
        def db = new groovy.sql.Sql(dataSource)
        try{
            def city = params['city']
            def districts = params['districts']
            //当前价格
            def priceSql =  " select sum(tt.currentUnitPrice) as currentUnitPrice , tt.cityName as cityName , tt.zoneNumber as zoneNumber from ( " +
                    " select d.name as cityName ,d.zone_Number as zoneNumber ,sum(e.latest_Evaluate_Price)/count(e.id)/sum(e.source_Build_Area) as currentUnitPrice " +
                    " from Collateral c join Estate e on c.estate_id = e.id and c.status = 'Completed' right join Area d on e.district_id = d.area_id where e.district_id is not null and e.city_id is not null and e.latest_Evaluate_Price is not null "

            //资产负值量
            def assetsBelowSql = " select sum(tt.assetsBelowCount) as assetsBelowCount , tt.cityName as cityName , tt.zoneNumber as zoneNumber from ( " +
                    " select d.name as cityName ,d.zone_Number as zoneNumber ,count(e.id)  as assetsBelowCount  " +
                    " from Collateral c join Estate e on c.estate_id = e.id and c.status = 'Completed' right join Area d on e.district_id = d.area_id " +
                    " where (e.latest_Evaluate_Price-e.first_Evaluate_Price) < 0 and e.district_id is not null and e.city_id is not null "
            //逾期总量
            def overdueSql = " select sum(tt.overdueCount) as overdueCount , tt.cityName as cityName , tt.zoneNumber as zoneNumber from ( " +
                    " select d.name as cityName ,d.zone_Number as zoneNumber ,count(e.id)  as overdueCount  " +
                    " from Collateral c join Estate e on c.estate_id = e.id and c.status = 'Completed' left join Mortgage m  on e.external_Id = m.external_Id right join Area d on e.district_id = d.area_id " +
                    " where m.mortgage_Status_id = (select id from Data_dict where class = 'com.cindata.dictionary.MortgageStatusDict' and code = '3') and e.district_id is not null and e.city_id is not null "
            def querySql = ""
            def sql = ""
            if (city && city != '-1')
            {
                querySql += " and d.parent_Area =(select area_id from Area  where zone_Number = '${city}')"
                sql +=" and d1.parent_Area = (select area_id from Area  where zone_Number = '${city}')"
            }
            if (districts)
            {
                def districtStr = "("+districts+")"
                querySql += " and d.area_id in "+ districtStr
                sql +=" and d1.area_id in "+ districtStr
            }
            def accountSql = Account.FilterAccountSql(User.findById(springSecurityService.principal.id))
            if (accountSql)
            {
                querySql += " and e.account_id in ${accountSql}"
            }
            querySql += "  group by d.name,d.zone_Number "
            priceSql += querySql
            assetsBelowSql += querySql
            overdueSql += querySql
            def unionSql = " union select d1.name as cityName,d1.zone_Number as zoneNumber,0 from area d1 " +
                    "  where d1.class='com.cindata.District'"
            unionSql += sql
            unionSql+="  group by d1.name,d1.zone_Number)  tt " +
                    "  group by tt.cityName , tt.zoneNumber order by tt.zoneNumber desc "
            priceSql += unionSql
            assetsBelowSql += unionSql
            overdueSql += unionSql
            def priceResult = db.rows(priceSql).asList()
            def assetsBelowResult = db.rows(assetsBelowSql).asList()
            def overdueResult = db.rows(overdueSql).asList()

            resultList. priceResult = priceResult
            resultList. assetsBelowResult = assetsBelowResult
            resultList. overdueResult = overdueResult

        }catch (Exception e){
            e.printStackTrace()
            status = "false"
        }
        render([resultList: resultList, status: status] as JSON)
    }

    //净值分析数据
    def netValueAnalyze(){
        def db = new groovy.sql.Sql(dataSource)
        params.max = params.max ? params.max.toInteger() : 10
        params.offset = params.offset ? params.offset.toInteger() : 0
        def max = params.max
        def offset = params.offset
        def city = params['city']
        def district = params['district']
        def loansDuration = params["loansDuration"]
        //贷款时间
        def repaymentRatio = params["repaymentRatio"]
        def cityNameList = componentService.evaluate(Component.findByCode('showCity'))
        def cityList = City.findAllByNameInList(cityNameList,[sort: 'name', order: 'asc'])
        if(!city && cityList){
            city = cityList[0].zoneNumber
        }
        def citySql = "a.zone_Number as zoneNumber,a.name as cityName "
        if (city && city != '-1')
        {
            citySql = "b.zone_Number as zoneNumber,b.name as cityName  "
        }
        def estateSql = " select count(e.id) as addedValueCount , (case when sum(e.latest_Evaluate_Price) is null then 0 else sum(e.latest_Evaluate_Price) end) as latestEvaluatePrice  ," +
                " (case when sum(e.first_Evaluate_Price) is null then 0 else sum(e.first_Evaluate_Price) end) as firstEvaluatePrice , (case when sum(m.loans_Balance) is null then 0 else sum(m.loans_Balance) end) as loansBalance ,"
        estateSql += citySql
        estateSql += " from Collateral c join Estate e on c.estate_id = e.id and c.status = 'Completed' left join Mortgage m on e.external_Id = m.external_Id left join Area a on a.area_id = e.city_id left join Area b on b.area_id = e.district_id where (e.latest_Evaluate_Price-e.first_Evaluate_Price) >0 and m.loans_Amount <>0 "

        def estateListSql = " select c.id as id ,e.external_Id as externalId ,d.name as buildingType , a.name as city ,b.name as district ," +
                " e.source_Address as sourceAddress ,e.source_Build_Area as sourceBuildArea ,(case when e.latest_Evaluate_Price is null then 0 else e.latest_Evaluate_Price end) as latestEvaluatePrice  ," +
                " (case when e.first_Evaluate_Price is null then 0 else e.first_Evaluate_Price end) as firstEvaluatePrice , " +
                " (case when m.loans_Balance is null then 0 else m.loans_Balance end) as loansBalance,e.price_Fluctuations_Ratio as priceFluctuationsRatio " +
                " from Collateral c join Estate e on c.estate_id = e.id and c.status = 'Completed' left join Mortgage m on e.external_Id = m.external_Id left join Area a on a.area_id = e.city_id left join Area b on b.area_id = e.district_id left join data_dict d on d.id = e.building_type_id and d.class = 'com.cindata.dictionary.BuildingTypeDict' where (e.latest_Evaluate_Price-e.first_Evaluate_Price) >0  and m.loans_Amount <>0 "
        def querySql = ""
        if (city && city != '-1')
        {
            querySql += " and a.zone_Number ='${city}' "
        }
        if (district && district != '-1')
        {
            querySql += " and b.zone_Number ='${district}' "
        }
        if (loansDuration && loansDuration != '')
        {
            def yearList = YearLimitDimension.list()
            yearList.each {
                if (it?.label == loansDuration)
                {
                    querySql += " and e.loans_Duration > ${it.minValue} and e.loans_Duration <= ${it.maxValue}  "
                }
            }
        }
        if(repaymentRatio){
            if(repaymentRatio == "已还0-50%"){
                querySql += " and (m.cur_Recycle_Total_Capital+m.cur_Recycle_Total_Interest)/m.loans_Amount > 0 and (m.cur_Recycle_Total_Capital+m.cur_Recycle_Total_Interest)/m.loans_Amount <= 0.5  "
            }
            else if(repaymentRatio == "已还50%-80%"){
                querySql += " and (m.cur_Recycle_Total_Capital+m.cur_Recycle_Total_Interest)/m.loans_Amount > 0.5 and (m.cur_Recycle_Total_Capital+m.cur_Recycle_Total_Interest)/m.loans_Amount <= 0.8  "
            }
            else if(repaymentRatio == "已还80%以上"){
                querySql += " and (m.cur_Recycle_Total_Capital+m.cur_Recycle_Total_Interest)/m.loans_Amount > 0.8  "
            }
        }
        def accountSql = Account.FilterAccountSql(User.findById(springSecurityService.principal.id))
        if (accountSql)
        {
            querySql += " and e.account_id in ${accountSql}"
        }
        estateListSql += querySql
        estateSql += querySql

        if (city && city != '-1')
        {
            estateSql += "  group by b.name,b.zone_Number order by b.zone_Number desc "
        }
        else
        {
            estateSql += " group by a.name,a.zone_Number order by a.zone_Number desc"
        }
        def countSql = " select count(e.id) from Collateral c join Estate e on c.estate_id = e.id and c.status = 'Completed' left join Mortgage m on e.external_Id = m.external_Id left join Area a on a.area_id = e.city_id left join Area b on b.area_id = e.district_id left join Data_dict d on d.id = e.building_type_id and d.class = 'com.cindata.dictionary.BuildingTypeDict'where (e.latest_Evaluate_Price-e.first_Evaluate_Price) >0 and m.loans_Amount <>0 "+ querySql
        def estateResult = db.rows(estateSql).asList()
        def addedValueAmountList = []
        def addedValueCountList = []
        def netValueAmountList = []
        def nameList = []
        estateResult?.each{
            nameList.add(it?.cityName)

            def netValueAmount = new BigDecimal((it?.latestEvaluatePrice-it?.firstEvaluatePrice-it?.loansBalance)/100000000).setScale(4,BigDecimal.ROUND_HALF_UP)
            netValueAmountList.add(netValueAmount)

            addedValueCountList.add(it?.addedValueCount?it?.addedValueCount:0)

            def addedValueAmount = new BigDecimal((it?.latestEvaluatePrice-it?.firstEvaluatePrice)/100000000).setScale(4,BigDecimal.ROUND_HALF_UP)
            addedValueAmountList.add(addedValueAmount)
        }
        def collateralList = db.rows(estateListSql,offset,max).asList()
        def collateralCount = db.rows(countSql)
        def districtList = District.findAllByParentAreaAndAreaLevel(City.findByZoneNumberAndAreaLevel(city, 2), 3)
        respond collateralList, model: [districtList: districtList, addedValueAmountList: addedValueAmountList, addedValueCountList: addedValueCountList,netValueAmountList: netValueAmountList,nameList: nameList,collateralList: collateralList, collateralCount :collateralCount[0].count]

    }

}
